﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Xml.Serialization;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.Common;

namespace Supermarkets
{
    public class ExcelReader
    {
        public DataTable ReadExcel(string dataSource)
        {
            // Set Access connection and select strings.
            // The path to BugTypes.MDB must be changed if you build 
            // the sample from the command line:


            string strAccessConn = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + dataSource + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";
            OleDbConnection myAccessConn = null;
            try
            {
                myAccessConn = new OleDbConnection(strAccessConn);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message);
                return null;
            }


            myAccessConn.Open();
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sales$]", myAccessConn);

            // Create new OleDbDataAdapter
            OleDbDataAdapter oleda = new OleDbDataAdapter();

            oleda.SelectCommand = cmd;

            // Create a DataSet which will hold the data extracted from the worksheet.
            DataSet ds = new DataSet();

            // Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(ds);
            DataTable dt = ds.Tables[0];

            //foreach (DataRow row in dt.Rows)
            //{
            //    foreach (DataColumn col in dt.Columns)
            //    {
            //        Console.Write(row[col]+ " ");
            //    }
            //    Console.WriteLine();
            //}

            return dt;
        }
    }
}